using System;
using System.Collections.Generic;
using System.Text;
using AF_ORM.CORE;
using System.Data;
using System.Data.SqlClient;
namespace Bussiness.Manager
{    
    public class CmsCategoryManager : Bussiness.ManagerBase.CmsCategoryManagerBase
    {
        public static CmsCategoryManager CreateInstant()
        {
            return new CmsCategoryManager();
        }
        
        /// <summary>
        /// Lay danh sach category theo loai va ngon ngu
        /// </summary>
        /// <param name="catType"></param>
        /// <param name="lang"></param>
        /// <returns></returns>
        public DataTable GetCategoryListByLang(string catType, string lang)
        {
            string sql = @"
                    select inf.*,cat.*
                    from cmsCategory cat inner join cmsCategoryInfos inf on cat.id = inf.categoryid and inf.lang = @lang and cat.CatType = @catType                        
                    
        ";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@catType", catType);
            param[1] = new SqlParameter("@lang", lang);
            return SqlHelper.ExecuteDataTable(Config.ConnectionConfig.ConnectionString, CommandType.Text, sql,param);
        }

        public DataTable GetCategoryListByLang(string catType, string lang,int IntID)
        {
            string sql = @"
                    select inf.*,cat.*
                    from cmsCategory cat inner join cmsCategoryInfos inf on cat.id = inf.categoryid and inf.lang = @lang and cat.CatType = @catType                        
                    
        ";
            if (IntID != -1)
                sql += " and cat.IntID=" + IntID;
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@catType", catType);
            param[1] = new SqlParameter("@lang", lang);
            return SqlHelper.ExecuteDataTable(Config.ConnectionConfig.ConnectionString, CommandType.Text, sql, param);
        }
        /// <summary>
        /// Lay danh sach category theo loai va ngon ngu
        /// </summary>
        /// <param name="catType"></param>
        /// <param name="lang"></param>
        /// <returns></returns>
        public DataTable GetCategoryTreeByLang(string catType, string lang)
        {
            string sql = @"
                    select distinct inf.*,Parent.catName as ParentName 
                    from 
                       ( select inf.*,cat.ParentId
                        from cmsCategory cat inner join cmsCategoryInfos inf on cat.id = inf.categoryid 
                                                       and inf.lang = @lang and cat.CatType = @catType   
                                                       
                        ) AS inf
                        LEFT JOIN
                        (select parent.Id, inf.CatName
                         from   cmsCategory cat inner join cmsCategory parent on cat.ParentId = parent.Id and cat.CatType = @catType
                                inner join cmsCategoryInfos inf on inf.CategoryId = Parent.Id and inf.Lang = @lang
                        ) as Parent on inf.ParentId = Parent.Id                     
                         
                    
        ";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@catType", catType);
            param[1] = new SqlParameter("@lang", lang);
            return SqlHelper.ExecuteDataTable(Config.ConnectionConfig.ConnectionString, CommandType.Text, sql, param);
        }

        public DataTable GetAllCategoryByCatType(string catType)
        {
            string sql = "select C.Id, C.IntId, C.CatType, C.ParentId, CF.Lang, CF.CatName, CF.CategoryId"
                       + " from cmsCategory as C inner join cmsCategoryInfos as CF"
                       + " on (C.Id = CF.CategoryId) where C.CatType = '" + catType + "'" ;
           
            return SqlHelper.ExecuteDataTable(Config.ConnectionConfig.ConnectionString, CommandType.Text, sql);
        }
    }
}
                    
